package com.jfinal.plugin.activerecord.sql;

import com.jfinal.kit.StrKit;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.Iterator;
import java.util.List;
import java.util.stream.Stream;

/**
 * SQL条件+排序+分页-构造器
 * <p>
 * Spec.where("id","=", 100) .and("name", "=", "xxx") .or("name", "like", "aaa")
 * .orderBy("id", "desc") .limit(10, 20)
 * <p>
 *
 * @author Chen
 * @date 15/5/26
 */
public class Spec {

    public static final String WHERE = "\n WHERE ";

    public static final String ORDER_BY = "\n ORDER BY ";
    public static final String GROUP_BY = "\n GROUP BY ";
    public static final String HAVING = "\n HAVING ";
    public static final String DESC = " DESC ";
    public static final String ASC = " ASC ";
    public static final String LIMIT = "\n LIMIT ";

    protected List<String> groupBys = new ArrayList<>();

    protected List<String> orderBys = new ArrayList<>();

    protected String having = "";
    protected String limit = "";

    private Cnd cnd = new Cnd();
    private Cnd havingCnd = new Cnd();

    private int pageNumber = 1;

    private int pageSize = 10;


    public static Spec create() {
        return new Spec();
    }

    public static Spec where(String sqlWhere, Object... params) {
        return new Spec().setCnd(Cnd.create(sqlWhere, params));
    }

    public static Spec where(Cnd cnd) {
        return new Spec().setCnd(cnd);
    }

    public static Spec where(String column, String op, Object value) {
        return new Spec().setCnd(Cnd.create(column, op, value));
    }

    private Spec setCnd(Cnd cnd) {
        this.cnd = cnd;
        return this;
    }

    public Spec and(Cnd cnd) {
        this.cnd.and(cnd);
        return this;
    }

    public Spec or(Cnd cnd) {
        this.cnd.or(cnd);
        return this;
    }

    public Spec and(String sqlWhere, Object... params) {
        this.cnd.and(sqlWhere, params);
        return this;
    }

    public Spec andEx(String column, String op, Object value) {
        if (value == null || StrKit.isBlank(value.toString())) {
            return this;
        }
        return and(column, op, value);
    }

    public Spec andEx(String sqlWhere, Object value) {
        if (value == null || StrKit.isBlank(value.toString())) {
            return this;
        }
        return and(sqlWhere, value);
    }

    public Spec andEx(Cnd cnd) {
        if (!(hasParam(cnd))) {
            return this;
        }
        return this.and(cnd);
    }

    private boolean hasParam(Cnd cnd) {
        for (Object param : cnd.getParams()) {
            if (param != null && StrKit.notBlank(param.toString())) {
                return true;
            }
        }
        return false;
    }

    public Spec orEx(Cnd cnd) {
        if (cnd.getParams().length == 0) {
            return this;
        }
        return this.or(cnd);
    }


    public Spec and(String column, String op, Object value) {
        this.cnd.and(column, op, value);
        return this;
    }

    public Spec orEx(String sqlWhere, Object value) {
        if (value == null || StrKit.isBlank(value.toString())) {
            return this;
        }
        return or(sqlWhere, value);
    }


    public Spec orEx(String column, String op, Object value) {
        if (value == null || StrKit.isBlank(value.toString())) {
            return this;
        }
        return or(column, op, value);
    }

    public Spec or(String sqlWhere, Object... params) {
        this.cnd.or(sqlWhere, params);
        return this;
    }

    public Spec or(String column, String op, Object value) {
        this.cnd.or(column, op, value);
        return this;
    }

    public Spec asc(String column) {
        return orderBy(column, ASC);
    }

    public Spec desc(String column) {
        return orderBy(column, DESC);
    }

    public Spec orderBy(String column, String order) {
        if (StrKit.isBlank(column) || StrKit.isBlank(order)) {
            return this;
        }
        this.orderBys.clear();
        this.orderBys.add(column + " " + order.toUpperCase());
        return this;
    }

    public Spec addOrderBy(String column, String order) {
        if (StrKit.isBlank(column) || StrKit.isBlank(order)) {
            return this;
        }
        this.orderBys.add(column + " " + order.toUpperCase());
        return this;
    }

    public Spec groupBy(String groupByStr) {
        if (StrKit.isBlank(groupByStr)) {
            return this;
        }

        this.groupBys.clear();
        this.groupBys.addAll(Arrays.asList(groupByStr.split(",")));
        return this;
    }

    public Spec addGroupBy(String groupBy) {
        if (StrKit.isBlank(groupBy)) {
            return this;
        }
        this.groupBys.add(groupBy);
        return this;
    }

    public Spec having(String having) {
        if (StrKit.isBlank(having)) {
            return this;
        }
        this.having = having;
        return this;
    }

    public Spec having(Cnd cnd) {
        if (cnd == null) {
            return this;
        }
        this.havingCnd = cnd;
        return this.having(this.havingCnd.sqlCnd);
    }

    public Spec havingAnd(String sqlWhere, Object... params) {
        this.havingCnd = this.havingCnd.and(sqlWhere, params);
        return this.having(this.havingCnd);
    }

    public Spec remove(String column, Object param) {
        if (StrKit.isBlank(column)) {
            return this;
        }
        if (!this.cnd.getSqlCnd().contains(column)) {
            return this;
        }
        String sqlCnd = this.cnd.getSqlCnd().replace(column + " AND", "");
        this.cnd.setSqlCnd(sqlCnd);
        Iterator<Object> itor = this.cnd.params.iterator();
        while (itor.hasNext()) {
            Object obj = itor.next();
            if (obj.equals(param)) {
                itor.remove();
                break;
            }
        }
        return this;
    }

    public int getPageNumber() {
        return pageNumber;
    }

    public int getPageSize() {
        return pageSize;
    }

    public Spec page(int start, int size) {
        this.pageNumber = start;
        this.pageSize = size;
        return this;
    }

    public Spec limit(int size) {
        return this.limit(0, size);
    }

    public Spec limit(int offset, int size) {
        this.limit = offset + ", " + size;
        int start = (offset / size) + 1;
        return this.page(start, size);
    }

    public String getGroupBySql() {
        return groupBys.isEmpty() ? "" : GROUP_BY + String.join(",", groupBys) + " ";
    }

    public String getOrderBySql() {
        return orderBys.isEmpty() ? "" : ORDER_BY + String.join(",", orderBys) + " ";
    }

    public String getHavingSql() {
        return StrKit.isBlank(having) ? "" : HAVING + having + " ";
    }

    public String getLimitSql() {
        return StrKit.isBlank(limit) ? "" : LIMIT + limit;
    }


    public Object[] getParams() {
        Object[] cndParams = cnd.getParams();
        if (this.havingCnd == null || this.havingCnd.getParams() == null || this.havingCnd.getParams().length == 0) {
            return cndParams;
        } else {
            List<Object> paramList = new ArrayList<>();
            if (cndParams != null) {
                Stream.of(cndParams).forEach(obj -> {
                    paramList.add(obj);
                });
            }
            Object[] havingCndParams = havingCnd.getParams();
            Stream.of(havingCndParams).forEach(obj -> {
                paramList.add(obj);
            });
            return paramList.toArray();
        }
    }

    public String toSql() {
        return new StringBuilder(toWhereSql())
                .append(getGroupBySql())
                .append(getHavingSql())
                .append(getOrderBySql())
                .append(getLimitSql()).toString();
    }

    public String toExceptSql() {
        return new StringBuilder(toWhereSql())
                .append(getGroupBySql())
                .append(getHavingSql())
                .append(getOrderBySql())
                .toString();
    }

    public String toWhereSql() {
        return where(cnd.toSql());
    }

    public Cnd getCnd() {
        return cnd;
    }

    private String where(String sql) {
        return StrKit.isBlank(sql) ? "" : WHERE + cnd.toSql();
    }


    @Override
    public String toString() {
        return this.getClass().getName() + " : " + toSql();
    }


    public static void main(String[] args) {

        Spec spec = Spec.where("id", "=", 100)
                .and("name", "=", "xxx")
                .and("name = ?", "abc")
                .or("name", "like", "aaa%")
                .orEx("name2", "like", "")
                .orEx("name2=?", "")
                .andEx(Cnd.create("aaa = ?", null).or("bbb = ?", ""))
                .orderBy("id", "desc")
                .groupBy("abc,xxx")
//                .having("abc>2")
//                .having(Cnd.create("def<?", 102))
//                .havingAnd("xxx<?", 999)
                .addGroupBy("ccc")
                .limit(10, 20);

        System.out.println("spec:" + spec.toSql());
        Object[] objects = spec.getParams();
        System.out.println("sped param:" + objects);


        SqlSelect sqlSelect = Sql.select("a,b,c")
                .addColumn("d")
                .from("table1 a")
                .leftJoin("table2 b", "a.id=b.aid")
                .where(spec);

        System.out.println(sqlSelect.toSql());

        System.out.println(sqlSelect.getSpec().getPageNumber());

    }


}
